/**********************************************************************
*Created/Source by	:sfibich
*ScriptName			:utility.scripts.scripts.sql
*Version			:1.0.120121
*Purpose			:This script loads scripts into the utility.sqlScripts
*					 table.
**********************************************************************/

BEGIN TRY
BEGIN TRANSACTION

	merge into utility.sqlScripts as target
	using (
		select script_id,script_name,script
		from
		(values
		(1,'Quick Count',N'
		select
		schema_name(o.schema_id) [schema_name]
		,o.name [object_name]
		,sum(ddps.row_count) row_count
		from
		sys.objects o
		inner join
		sys.dm_db_partition_stats ddps
		on
		o.object_id =ddps.object_id 
		where
		ddps.index_id in (1,0)
		and
		o.type not in (''S'',''IT'')
		group by
		schema_name(o.schema_id)
		,o.name 
		order by 
		schema_name(o.schema_id)
		,o.name ')
		) as insertTable(script_id,script_name,script)
	) as source
	on target.script_id=source.script_id
	when matched and target.script<>source.script then 
		update set target.script=source.script
		,target.date_created=getdate()
	when not matched then
		insert (script_id,script_name,script,date_created)
		values (script_id,script_name,script,getdate());

	merge metadata.utilityInfo as t
	using (
	select 
	'utility.scripts' as info_desc
	,'1.0.120112' as value
	) as s
	on
	(s.info_desc=t.info_desc)
	when matched then
	update 
	set 
	t.value=s.value
	,t.date_time=getdate()
	when not matched then 
	insert
	(info_desc,value)
	values
	(s.info_desc,s.value);
COMMIT TRANSACTION
END TRY
BEGIN CATCH
	PRINT error_message()
	ROLLBACK
END CATCH
